--
-- Description: View for displaying the rolled up data by locale for events.
-- Date created: 12 May 2008
--

set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go

--
-- drop the view if it exists and recreate it.
--
if exists
(
	select * 
	from sys.views
	where object_id = OBJECT_ID(N'[dbo].[EventHitsLocaleRollup_vw]')
)
begin
	drop view [dbo].[EventHitsLocaleRollup_vw]
end
go

create view [dbo].[EventHitsLocaleRollup_vw] 
as
	select
	EH.[EventID]
	, ET.[EventTypeName]
	, EH.[LocaleID]
	, L.[LocaleCode]
	, L.[LocaleName]
	, SUM(EH.[TotalHits]) TotalHits
	from [dbo].[EventHits] EH (nolock)
	inner join [dbo].[EventTypes] ET (nolock)
		on EH.EventTypeID = ET.EventTypeID
	inner join [dbo].[Locales] L (nolock)
		on EH.LocaleID = L.LocaleID
	group by EH.EventID
			, ET.EventTypeName
			, EH.LocaleID
			, L.LocaleCode
			, L.LocaleName
go
